using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class HocSinhDAO
    {
        //Inserting
        public static bool InsertHocSinh(HocSinhDAO emp)
        {
            bool result=false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MAHOCSINH", emp.MAHOCSINH));
                sqlParams.Add(new SqlParameter("@MATONGIAO", emp.MATONGIAO));
                sqlParams.Add(new SqlParameter("@MADANTOC", emp.MADANTOC));
                sqlParams.Add(new SqlParameter("@MALOP", emp.MALOP));
                sqlParams.Add(new SqlParameter("@HOTEN", emp.HOTEN));
                sqlParams.Add(new SqlParameter("@GIOITINH", emp.GIOTINH));
                sqlParams.Add(new SqlParameter("@NGAYSINH", emp.NGAYSINH));
                sqlParams.Add(new SqlParameter("@NOISINH", emp.NOISINH));
                sqlParams.Add(new SqlParameter("@DIACHI", emp.DIACHI));
                sqlParams.Add(new SqlParameter("@DIENTHOAI", emp.DIENTHOAI));
                sqlParams.Add(new SqlParameter("@TECHA", emp.TENCHA));
                sqlParams.Add(new SqlParameter("@NGHENGHIEPCHA", emp.NGHENGHIEPCHA));
                sqlParams.Add(new SqlParameter("@TENME", emp.TENME));
                sqlParams.Add(new SqlParameter("@NGHENGHIEPME", emp.NGHENGHIEPME));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spInsertHocSinh", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        //Updating
        public static bool UpdateHocSinh(HocSinhDTO emp)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MAHOCSINH", emp.MAHOCSINH));
                sqlParams.Add(new SqlParameter("@MATONGIAO", emp.MATONGIAO));
                sqlParams.Add(new SqlParameter("@MADANTOC", emp.MADANTOC));
                sqlParams.Add(new SqlParameter("@MALOP", emp.MALOP));
                sqlParams.Add(new SqlParameter("@HOTEN", emp.HOTEN));
                sqlParams.Add(new SqlParameter("@GIOITINH", emp.GIOTINH));
                sqlParams.Add(new SqlParameter("@NGAYSINH", emp.NGAYSINH));
                sqlParams.Add(new SqlParameter("@NOISINH", emp.NOISINH));
                sqlParams.Add(new SqlParameter("@DIACHI", emp.DIACHI));
                sqlParams.Add(new SqlParameter("@DIENTHOAI", emp.DIENTHOAI));
                sqlParams.Add(new SqlParameter("@TECHA", emp.TENCHA));
                sqlParams.Add(new SqlParameter("@NGHENGHIEPCHA", emp.NGHENGHIEPCHA));
                sqlParams.Add(new SqlParameter("@TENME", emp.TENME));
                sqlParams.Add(new SqlParameter("@NGHENGHIEPME", emp.NGHENGHIEPME));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExecuteNoneQuery("spUpdateHocSinh", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        // Deleting
        public static bool DeleteMaHocSinh(String MAHOCSINH)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MAHOCSINH", MAHOCSINH));
                // Call Store Procedure
                int n = OleDbDataAccessHelper.ExecuteNoneQuery("spDeleteMaHocSinh", sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        // Retrieving
        //public static List<HocSinhDTO> SelectEmployeeByEmployeeStyle(int employeeStyle)
        //{
        //    List<EmployeeDTO> list = new List<EmployeeDTO>();
        //    try
        //    {
        //        // Create List Sql Parameter
        //        List<SqlParameter> sqlParams = new List<SqlParameter>();
        //        sqlParams.Add(new SqlParameter("@EmployeeStyle", employeeStyle));
        //        DataTable dt = SqlDataAccessHelper.ExecuteQuery("spSelectEmployeeByEmployeeStyle", sqlParams);
        //        foreach (DataRow dr in dt.Rows)
        //        {
        //            EmployeeDTO emp = new EmployeeDTO();
        //            emp.EmployeeID = dr["EmployeeID"].ToString();
        //            emp.Name = dr["Name"].ToString();
        //            emp.Email = dr["Email"].ToString();
        //            emp.Salary = float.Parse(dr["Salary"].ToString ());
        //            emp.EmployeeStyle = int.Parse(dr["EmployeeStyle"].ToString ());
        //            list.Add(emp);
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    return list;
        //}
        public static List<HocSinhDTO> SelectTatCaHocSinh()
        {
            List<HocSinhDTO> list = new List<HocSinhDTO>();
            try
            {
               DataTable dt = OleDbDataAccessHelper.ExecuteQuery("spSelectTatCaHocSinh");
                foreach (DataRow dr in dt.Rows)
                {
                    HocSinhDTO emp = new HocSinhDTO();
                    emp.MAHOCSINH = int.Parse(dr["MAHOCSINH"].ToString());
                    emp.MATONGIAO = int.Parse(dr["MATONGIAO"].ToString());
                    emp.MADANTOC = int.Parse(dr["MADANTOC"].ToString());
                    emp.MALOP = int.Parse(dr["MALOP"].ToString());
                    emp.HOTEN = dr["HOTEN"].ToString();
                    emp.GIOITINH = Boolean.Parse(dr["NAM"].ToString());
                    emp.NGAYSINH = DateTime.Parse(dr["NGAYSINH"].ToString());
                    emp.NOISINH = dr["MAHOCSINH"].ToString();
                    emp.DIACHI = dr["DIACHI"].ToString();
                    emp.DIENTHOAI = dr["DIENTHOAI"].ToString();
                    emp.TENCHA = dr["TENCHA"].ToString();
                    emp.NGHENGHIEPCHA = dr["NGHENGHIEPCHA"].ToString();
                    emp.TENME = dr["TENME"].ToString();
                    emp.NGHENGHIEPME = dr["NGHENGHIEPME"].ToString();
                 
                    list.Add(emp);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }
        public static HocSinhDTO SelectMaHocSinh(String MaHocSinh)
        {
            HocSinhDTO emp = new HocSinhDTO();
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MAHOCSINH", MaHocSinh));

                DataTable dt = OleDbDataAccessHelper.ExecuteQuery("spSelectMaHocSinh", sqlParams);
                DataRow dr = dt.Rows[0];
                emp.MAHOCSINH = int.Parse(dr["MAHOCSINH"].ToString());
                emp.MATONGIAO = int.Parse(dr["MATONGIAO"].ToString());
                emp.MADANTOC = int.Parse(dr["MADANTOC"].ToString());
                emp.MALOP = int.Parse(dr["MALOP"].ToString());
                emp.HOTEN = dr["HOTEN"].ToString();
                emp.GIOITINH = Boolean.Parse(dr["NAM"].ToString());
                emp.NGAYSINH = DateTime.Parse(dr["NGAYSINH"].ToString());
                emp.NOISINH = dr["MAHOCSINH"].ToString();
                emp.DIACHI = dr["DIACHI"].ToString();
                emp.DIENTHOAI = dr["DIENTHOAI"].ToString();
                emp.TENCHA = dr["TENCHA"].ToString();
                emp.NGHENGHIEPCHA = dr["NGHENGHIEPCHA"].ToString();
                emp.TENME = dr["TENME"].ToString();
                emp.NGHENGHIEPME = dr["NGHENGHIEPME"].ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return emp;
        }
        public static bool CheckMaHocSinh(String MaHocSinh)
        {
            bool result = false;
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@MAHOCSINH", MaHocSinh));

                DataTable dt = OleDbDataAccessHelper.ExecuteQuery("spSelectMaHocSinh", sqlParams);
                if (dt.Rows.Count == 1)
                {
                    result = true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
    }
}
